"""
Recipients API routes - Recipient Management
Using raw SQL queries with psycopg2 (NO ORM!)
"""

from flask import Blueprint, request, jsonify
from database.connection import execute_query

bp = Blueprint('recipients', __name__, url_prefix='/api/recipients')

@bp.route('', methods=['GET'])
def get_recipients():
    """
    Get all recipients with optional filtering
    GET /api/recipients?keyword=xxx&page=1&pageSize=10
    """
    try:
        keyword = request.args.get('keyword', '').strip()
        page = int(request.args.get('page', 1))
        page_size = int(request.args.get('pageSize', 10))
        
        query = """
            SELECT r.recipient_id, r.full_name, r.date_of_birth,
                   r.contact_email, r.contact_phone, r.address, r.registration_date,
                   -- 只有无订单的接收方才能删除
                   NOT EXISTS(SELECT 1 FROM orders WHERE recipient_id = r.recipient_id) as can_delete
            FROM recipients r
            WHERE 1=1
        """
        
        params = []
        
        # 关键词搜索（姓名、邮箱或电话）
        if keyword:
            query += " AND (full_name ILIKE %s OR contact_email ILIKE %s OR contact_phone ILIKE %s)"
            search_pattern = f'%{keyword}%'
            params.extend([search_pattern, search_pattern, search_pattern])
        
        query += " ORDER BY recipient_id ASC"
        
        # 分页
        offset = (page - 1) * page_size
        query += " LIMIT %s OFFSET %s"
        params.extend([page_size, offset])
        
        recipients = execute_query(query, tuple(params) if params else None)
        
        # 获取总数
        count_query = """
            SELECT COUNT(*) as count
            FROM recipients
            WHERE 1=1
        """
        count_params = []
        
        if keyword:
            count_query += " AND (full_name ILIKE %s OR contact_email ILIKE %s OR contact_phone ILIKE %s)"
            search_pattern = f'%{keyword}%'
            count_params.extend([search_pattern, search_pattern, search_pattern])
        
        count_result = execute_query(count_query, tuple(count_params) if count_params else None, fetch_one=True)
        total_count = count_result['count'] if count_result else 0
        
        return jsonify({
            'code': 200,
            'message': 'success',
            'data': recipients,
            'count': total_count
        }), 200
        
    except Exception as e:
        return jsonify({
            'code': 500,
            'message': str(e)
        }), 500

@bp.route('/<int:recipient_id>', methods=['GET'])
def get_recipient(recipient_id):
    """
    Get a single recipient by ID with order history
    GET /api/recipients/:id
    """
    try:
        query = """
            SELECT r.*, COUNT(DISTINCT o.order_id) as total_orders
            FROM recipients r
            LEFT JOIN orders o ON r.recipient_id = o.recipient_id
            WHERE r.recipient_id = %s
            GROUP BY r.recipient_id
        """
        
        recipient = execute_query(query, (recipient_id,), fetch_one=True)
        
        if not recipient:
            return jsonify({
                'code': 404,
            'message': 'Recipient not found'
        }), 404
        
        return jsonify({
            'code': 200,
            'message': 'success',
            'data': recipient
        }), 200
        
    except Exception as e:
        return jsonify({
            'code': 500,
            'message': str(e)
        }), 500

@bp.route('', methods=['POST'])
def create_recipient():
    """
    Create a new recipient
    POST /api/recipients
    """
    try:
        data = request.json
        
        required_fields = ['full_name', 'date_of_birth']
        for field in required_fields:
            if field not in data:
                return jsonify({
                    'code': 400,
                    'message': f'Missing required field: {field}'
                }), 400
        
        query = """
            INSERT INTO recipients (full_name, date_of_birth, contact_email,
                                   contact_phone, address)
            VALUES (%s, %s, %s, %s, %s)
            RETURNING recipient_id, full_name, registration_date
        """
        
        params = (
            data['full_name'],
            data['date_of_birth'],
            data.get('contact_email'),
            data.get('contact_phone'),
            data.get('address')
        )
        
        result = execute_query(query, params, fetch_one=True)
        
        return jsonify({
            'code': 200,
            'message': 'Recipient created successfully',
            'data': result
        }), 201
        
    except Exception as e:
        error_message = str(e)
        
        # 解析数据库约束错误，返回友好的错误信息
        if 'chk_recipients_age' in error_message:
            return jsonify({
                'code': 400,
                'message': '接收方年龄必须在18岁以上'
            }), 400
        elif 'chk_recipients_phone' in error_message:
            return jsonify({
                'code': 400,
                'message': '电话号码格式不正确：必须是"+"开头，后跟国家代码和7-15位号码（如 +8613800138000）'
            }), 400
        elif 'uq_recipients_email' in error_message:
            return jsonify({
                'code': 400,
                'message': '该邮箱已被使用'
            }), 400
        elif 'uq_recipients_phone' in error_message:
            return jsonify({
                'code': 400,
                'message': '该电话号码已被使用'
            }), 400
        
        return jsonify({
            'code': 500,
            'message': str(e)
        }), 500

@bp.route('/<int:recipient_id>', methods=['PUT'])
def update_recipient(recipient_id):
    """
    Update a recipient
    PUT /api/recipients/:id
    """
    try:
        data = request.json
        
        update_fields = []
        params = []
        
        allowed_fields = ['full_name', 'contact_email', 'contact_phone', 'address']
        
        for field in allowed_fields:
            if field in data:
                update_fields.append(f"{field} = %s")
                params.append(data[field])
        
        if not update_fields:
            return jsonify({
                'code': 400,
            'message': 'No fields to update'
        }), 400
        
        params.append(recipient_id)
        
        query = f"""
            UPDATE recipients
            SET {', '.join(update_fields)}
            WHERE recipient_id = %s
            RETURNING recipient_id, full_name
        """
        
        result = execute_query(query, tuple(params), fetch_one=True)
        
        if not result:
            return jsonify({
                'code': 404,
            'message': 'Recipient not found'
        }), 404
        
        return jsonify({
            'code': 200,
            'message': 'Recipient updated successfully',
            'data': result
        }), 200
        
    except Exception as e:
        return jsonify({
            'code': 500,
            'message': str(e)
        }), 500

@bp.route('/<int:recipient_id>', methods=['DELETE'])
def delete_recipient(recipient_id):
    """
    Delete a recipient
    DELETE /api/recipients/:id
    """
    try:
        # 检查接收方是否存在
        check_query = "SELECT * FROM recipients WHERE recipient_id = %s"
        recipient = execute_query(check_query, (recipient_id,), fetch_one=True)
        
        if not recipient:
            return jsonify({
                'code': 404,
                'message': 'Recipient not found'
            }), 404
        
        # 检查是否有订单
        order_check_query = """
            SELECT COUNT(*) as count
            FROM orders
            WHERE recipient_id = %s
        """
        order_result = execute_query(order_check_query, (recipient_id,), fetch_one=True)
        
        if order_result and order_result['count'] > 0:
            return jsonify({
                'code': 400,
                'message': f'无法删除：该接收方有 {order_result["count"]} 个订单记录。为保留订单历史，不可删除。'
            }), 400
        
        # 物理删除
        delete_query = """
            DELETE FROM recipients
            WHERE recipient_id = %s
            RETURNING recipient_id, full_name
        """
        
        result = execute_query(delete_query, (recipient_id,), fetch_one=True)
        
        return jsonify({
            'code': 200,
            'message': 'Recipient deleted successfully',
            'data': result
        }), 200
        
    except Exception as e:
        return jsonify({
            'code': 500,
            'message': str(e)
        }), 500

@bp.route('/<int:recipient_id>/orders', methods=['GET'])
def get_recipient_orders(recipient_id):
    """
    Get all orders for a specific recipient
    GET /api/recipients/:id/orders
    """
    try:
        query = """
            SELECT o.order_id, o.order_date, o.status, o.total_amount,
                   pc.clinic_name, COUNT(oi.vial_id) as vial_count
            FROM orders o
            LEFT JOIN partner_clinics pc ON o.clinic_id = pc.clinic_id
            LEFT JOIN order_items oi ON o.order_id = oi.order_id
            WHERE o.recipient_id = %s
            GROUP BY o.order_id, pc.clinic_name
            ORDER BY o.order_id ASC
        """
        
        orders = execute_query(query, (recipient_id,))
        
        return jsonify({
            'code': 200,
            'message': 'success',
            'data': orders,
            'count': len(orders)
        }), 200
        
    except Exception as e:
        return jsonify({
            'code': 500,
            'message': str(e)
        }), 500

@bp.route('/search', methods=['GET'])
def search_recipients():
    """
    Search recipients by name, code, or email
    GET /api/recipients/search?q=xxx
    """
    try:
        search_term = request.args.get('q', '')
        
        if not search_term:
            return jsonify({
                'code': 400,
            'message': 'Search term is required'
        }), 400
        
        query = """
            SELECT recipient_id, full_name, contact_email, contact_phone, registration_date
            FROM recipients
            WHERE full_name ILIKE %s OR contact_email ILIKE %s
            ORDER BY recipient_id
        """
        
        search_pattern = f'%{search_term}%'
        recipients = execute_query(query, (search_pattern, search_pattern))
        
        return jsonify({
            'code': 200,
            'message': 'success',
            'data': recipients,
            'count': len(recipients)
        }), 200
        
    except Exception as e:
        return jsonify({
            'code': 500,
            'message': str(e)
        }), 500

